package com.william.jdbcplus.core.dialect.meta.impl;

import com.william.jdbcplus.core.dialect.meta.IMeta;
import com.william.jdbcplus.core.toolkit.StringUtil;
import com.william.jdbcplus.core.util.JdbcUtil;
import com.william.jdbcplus.core.util.datameta.*;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 获取数据表信息基类
 *
 * @author william
 */
public class BaseMeta implements IMeta {

    /**
     * 获取数据库表字段信息列表
     *
     * @param ds
     * @return
     */
    @Override
    public List<TableMetaColumnInfo> getColumnMeta(DataSource ds, String tableName) {
        List<TableMetaColumnInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            // 查询表中的所有字段
            resultSet = dbMetaData.getColumns(catalog, schema, tableName, "%");
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                int dataType = resultSet.getInt("DATA_TYPE");
                String dataTypeName = resultSet.getString("TYPE_NAME");
                int columnSize = resultSet.getInt("COLUMN_SIZE");
                int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");
                int numPrecRadix = resultSet.getInt("NUM_PREC_RADIX");
                int nullAble = resultSet.getInt("NULLABLE");
                String remarks = resultSet.getString("REMARKS");
                String columnDef = resultSet.getString("COLUMN_DEF");
                int sqlDataType = resultSet.getInt("SQL_DATA_TYPE");
                int sqlDatetimeSub = resultSet.getInt("SQL_DATETIME_SUB");
                int charOctetLength = resultSet.getInt("CHAR_OCTET_LENGTH");
                int ordinalPosition = resultSet.getInt("ORDINAL_POSITION");
                String isNullAble = resultSet.getString("IS_NULLABLE");
                //  String isAutoincrement = resultSet.getString("IS_AUTOINCREMENT"); //ORACLE数据库报错
                TableMetaColumnInfo columnInfo = TableMetaColumnInfo.builder()
                        .tableName(tableName)
                        .columnName(columnName)
                        .dataType(dataType)
                        .dataTypeName(dataTypeName)
                        .columnSize(columnSize)
                        .decimalDigits(decimalDigits)
                        .numPrecRadix(numPrecRadix)
                        .nullAble(nullAble == 1)
                        .columnRemarks(remarks)
                        .columnDef(columnDef)
                        .sqlDataType(sqlDataType)
                        .sqlDatetimeSub(sqlDatetimeSub)
                        .charOctetLength(charOctetLength)
                        .ordinalPosition(ordinalPosition)
                        .isNullAble(isNullAble).build();
                result.add(columnInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }
        return result;
    }

    /**
     * 获取数据库主键信息列表
     *
     * @param ds
     * @return
     */
    @Override
    public List<TableMetaPkInfo> getPkMeta(DataSource ds, String tableName) {
        List<TableMetaPkInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            resultSet = dbMetaData.getPrimaryKeys(catalog, schema, tableName);
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                short keySeq = resultSet.getShort("KEY_SEQ");
                String pkName = resultSet.getString("PK_NAME");
                TableMetaPkInfo pkInfo = TableMetaPkInfo.builder()
                        .tableName(tableName)
                        .columnName(columnName)
                        .keySeq(keySeq)
                        .pkName(pkName)
                        .build();
                result.add(pkInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }
        return result;
    }

    /**
     * 获取数据库外键信息列表
     *
     * @param ds
     * @return
     */
    @Override
    public List<TableMetaFkInfo> getFkMeta(DataSource ds, String tableName) {
        List<TableMetaFkInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            resultSet = dbMetaData.getExportedKeys(catalog, schema, tableName);
            while (resultSet.next()) {
                String pkTableName = resultSet.getString("PKTABLE_NAME");
                String pkColumnName = resultSet.getString("PKCOLUMN_NAME");
                String fkTableName = resultSet.getString("FKTABLE_NAME");
                String fkColumnName = resultSet.getString("FKCOLUMN_NAME");
                short keySeq = resultSet.getShort("KEY_SEQ");
                String fkName = resultSet.getString("FK_NAME");
                String pkName = resultSet.getString("PK_NAME");
                TableMetaFkInfo fkInfo = TableMetaFkInfo.builder()
                        .tableName(fkTableName)
                        .pkTableName(pkTableName)
                        .pkColumnName(pkColumnName)
                        .fkTableName(fkTableName)
                        .fkColumnName(fkColumnName)
                        .fkName(fkName)
                        .pkName(pkName)
                        .keySeq(keySeq)
                        .build();
                result.add(fkInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }
        return result;
    }

    /**
     * 获取数据库索引信息列表
     *
     * @param ds
     * @return
     */
    @Override
    public List<TableMetaIndexInfo> getIndexMeta(DataSource ds, String tableName) {
        List<TableMetaIndexInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            // 查询表中的所有字段
            resultSet = dbMetaData.getIndexInfo(catalog, schema, tableName, false, true);
            while (resultSet.next()) {
                boolean nonUnique = resultSet.getBoolean("NON_UNIQUE");
                String indexQualifier = resultSet.getString("INDEX_QUALIFIER");
                String indexName = resultSet.getString("INDEX_NAME");
                short type = resultSet.getShort("TYPE");
                short ordinalPosition = resultSet.getShort("ORDINAL_POSITION");
                String columnName = resultSet.getString("COLUMN_NAME");
                String ascOrDesc = resultSet.getString("ASC_OR_DESC");
                int cardinality = resultSet.getInt("CARDINALITY");
                TableMetaIndexInfo indexInfo = TableMetaIndexInfo.builder()
                        .tableName(tableName)
                        .nonUnique(nonUnique)
                        .indexName(indexName)
                        .type(type)
                        .ordinalPosition(ordinalPosition)
                        .columnName(columnName)
                        .ascOrDesc(ascOrDesc)
                        .cardinality(cardinality)
                        .build();
                result.add(indexInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }
        return result;

    }

    /**
     * 获取数据表信息列表
     *
     * @param ds
     * @return
     */
    @Override
    public List<TableMetaInfo> getTableMeta(DataSource ds) {
        List<TableMetaFkInfo> fkInfoList = getFkMetaList(ds);

        List<TableMetaInfo> tableList = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            // 初始化获得表的信息
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            resultSet = dbMetaData.getTables(catalog, schema, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                if (tableName.contains("$") || tableName.contains("/")) {
                    continue;
                }
                String tableType = resultSet.getString("TABLE_TYPE");
                String tableRemarks = resultSet.getString("REMARKS");
                if (StringUtil.isBlank(tableRemarks)) {
                    tableRemarks = tableName;
                }

                List<TableMetaFkInfo> fkList = fkInfoList.stream().filter(e -> e.getTableName().equals(tableName)).collect(Collectors.toList());
                TableMetaInfo metaInfo = TableMetaInfo.builder()
                        .tableName(tableName)
                        .tableRemarks(tableRemarks)
                        .tableType(tableType)
                        .columnList(getColumnMeta(ds, tableName))
                        .pkList(getPkMeta(ds, tableName))
                        .fkList(fkList)
                        .indexList(getIndexMeta(ds, tableName))
                        .build();
                tableList.add(metaInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return tableList;
    }

    /**
     * 获取数据表信息列表
     *
     * @param ds
     * @param tableName
     * @return
     */
    @Override
    public TableMetaInfo getTableMeta(DataSource ds, String tableName) {
        TableMetaInfo result = null;
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            // 初始化获得表的信息
            connection = ds.getConnection();
            String catalog = connection.getCatalog();
            String schema = connection.getSchema();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            resultSet = dbMetaData.getTables(catalog, schema, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String table = resultSet.getString("TABLE_NAME");
                if (!table.equals(tableName)) {
                    continue;
                }
                String tableType = resultSet.getString("TABLE_TYPE");
                String tableRemarks = resultSet.getString("REMARKS");
                if (StringUtil.isBlank(tableRemarks)) {
                    tableRemarks = tableName;
                }

                result = TableMetaInfo.builder()
                        .tableName(tableName)
                        .tableRemarks(tableRemarks)
                        .tableType(tableType)
                        .columnList(getColumnMeta(ds, tableName))
                        .indexList(getIndexMeta(ds, tableName))
                        .pkList(getPkMeta(ds, tableName))
                        .fkList(getFkMeta(ds, tableName))
                        .build();

                break;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }

    /**
     * 获取数据库信息
     *
     * @param ds
     * @return
     */
    @Override
    public DatabaseMetaInfo getDbMeta(DataSource ds) {
        DatabaseMetaInfo databaseInfo = null;
        Connection connection = null;
        try {
            // 初始化获得表的信息
            connection = ds.getConnection();
            DatabaseMetaData dbMetaData = connection.getMetaData();
            databaseInfo = DatabaseMetaInfo.builder()
                    .userName(dbMetaData.getUserName())
                    .systemFunctions(dbMetaData.getSystemFunctions())
                    .timeDateFunctions(dbMetaData.getTimeDateFunctions())
                    .stringFunctions(dbMetaData.getStringFunctions())
                    .schemaTerm(dbMetaData.getSchemaTerm())
                    .URL(dbMetaData.getURL())
                    .readOnly(dbMetaData.isReadOnly())
                    .databaseProductName(dbMetaData.getDatabaseProductName())
                    .databaseProductVersion(dbMetaData.getDatabaseProductVersion())
                    .driverName(dbMetaData.getDriverName())
                    .driverVersion(dbMetaData.getDriverVersion())
                    .tableInfoList(getTableMeta(ds))
                    .build();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return databaseInfo;
    }

    @Override
    public List<TableMetaFkInfo> getFkMetaList(DataSource ds) {
        List<TableMetaFkInfo> result = new ArrayList<>();
        List<String> tableNames = JdbcUtil.getTables(ds);
        Connection connection = null;
        ResultSet resultSet = null;
        for (String tableName : tableNames) {
            try {
                connection = ds.getConnection();
                DatabaseMetaData dbMetaData = connection.getMetaData();
                resultSet = dbMetaData.getExportedKeys(connection.getCatalog(), connection.getSchema(), tableName);
                while (resultSet.next()) {
                    String pkTableName = resultSet.getString("PKTABLE_NAME");
                    String pkColumnName = resultSet.getString("PKCOLUMN_NAME");
                    String fkTableName = resultSet.getString("FKTABLE_NAME");
                    String fkColumnName = resultSet.getString("FKCOLUMN_NAME");
                    short keySeq = resultSet.getShort("KEY_SEQ");
                    String fkName = resultSet.getString("FK_NAME");
                    String pkName = resultSet.getString("PK_NAME");
                    TableMetaFkInfo fkInfo = TableMetaFkInfo.builder()
                            .tableName(fkTableName)
                            .pkTableName(pkTableName)
                            .pkColumnName(pkColumnName)
                            .fkTableName(fkTableName)
                            .fkColumnName(fkColumnName)
                            .fkName(fkName)
                            .pkName(pkName)
                            .keySeq(keySeq)
                            .build();
                    result.add(fkInfo);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (null != resultSet) {
                        resultSet.close();
                    }
                    if (null != connection) {
                        connection.close();
                    }
                } catch (SQLException ex) {
                }
            }
        }

        return result;
    }
}
